<?php
namespace  app\zylib\controller;
/**
 * @Author: user
 * @Date:   2019-05-18 13:24:23
 * @Last Modified by:   user
 * @Last Modified time: 2019-05-18 
 */
use think\Controller;
use think\Loader;//引入此文件
use app\zylib\controller\ApiZyBaseController;
use think\Exception;
/**
 * 文件操作控制器类
 */
class ApiFileController extends ApiZyBaseController
{
	//执行构造 进行初始化工作
	function _initialize()
	{
		parent::_initialize();
		Loader::import('PHPExcel', __DIR__."/../lib/zy_file/phpoffice/phpexcel/Classes",'.php');
	}
	/**
	 * @method inportExcel excel导入方法
	 * @param [array]  $data 要导入的数据列表
	 * @param [array] $field 表头字段信息
	 * @example 表头字段  $field=[['id','编号'],['name','昵称']]
	 * @example 数据列表 $data=[['id'=>1,'name'=>'天王'],['id'=>2,'name'=>'盖地虎']]
	 * @return 成功输出cexel文件 错误返回json格式错误信息 
	 * 错误信息说明：
	 * 		101：未传递参数 field
	 * 		102：未传递参数  data
	 * 备注：导出时传递的field 和data字段数据要匹配起来，在导出的表中没有得到想要的数据，大部分是这个原因造成的。
	 */
	public function inportExcel()
	{
		$data=$this->request->param();
		if(!isset($data['data']) || empty($data['data']) || !is_array($data['data'])){
			return zy_json(false,'未正确传递参数',null,100);
		}
		if(!isset($data['field']) || empty($data['field'])  || !is_array($data['field'])){
			return zy_json(false,'未正确传递参数',null,101);
		}		
		$expCellName=$data['field'];
		$expTableData=$data['data'];
        //include_once EXTEND_PATH.'PHPExcel/PHPExcel.php';//方法二
       // $xlsTitle = iconv('utf-8', 'gb2312', $data['name']);//文件名称
        $fileName = date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum =count($expTableData);
        $objPHPExcel = new \PHPExcel();//方法一
        //$objPHPExcel = new \PHPExcel();//方法二
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $fileName.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
        	$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
        	for($j=0;$j<$cellNum;$j++){
        		$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
        	}
        }
        ob_end_clean();//这一步非常关键，用来清除缓冲区防止导出的excel乱码
       	header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$fileName.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//"xls"参考下一条备注
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls
        $objWriter->save('php://output');
        //$objWriter->save('demo.xls');
        exit;
    }

    /**
     * excel导出数据为数组
     *@param $filePath 文件地址
     *@param $startRow	从第几行开始读取内容 默认第一行开始读取
     *@param $field   转换字段 不传递此参数 默认列标号
     *@return 成功返回携带数据的json
     *状态码解释：
     *		100     未传递参数filePath 
	*		101     文件类型错误  文件只支持 xls/xlsx/csv
	*		102     请求文件时发生错误，请检查文件地址
	*		103     文件不存在
     */
    public function exportExcel()
    {
    	$data=$this->request->param();
    	if(!isset($data['filePath']) || empty($data['filePath']) ){
			echo zy_json(false,'参数缺失',null,100);exit;
    	}
    	if(!isset($data['field']) || empty($data['field']) ){
			$data['field']=[];
    	}
    	if(!isset($data['startRow']) || empty($data['startRow']) || intval($data['startRow'])<=0 ){
			$data['startRow']=0;
    	}
    	//加载excel文件
    	$fileType=array_filter(explode('.',trim($data['filePath'])));//文件类型
        $exts=end($fileType);
        if(!in_array($exts,['xls','xlsx','csv'])){
        	echo zy_json(false,'文件类型错误！',null,101);exit;
        }
        //读取文件
        $ch = curl_init(); 
		curl_setopt ($ch, CURLOPT_URL, $data['filePath']); 
		curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1); 
		curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT,10); 
		$fileName = curl_exec($ch); 
		$info=curl_getinfo($ch);
		curl_close($ch);
		if(false===$fileName){
			echo zy_json(false,'请求文件时发生错误！',null,102);exit;
		}
		if(!in_array($info['content_type'],['application/octet-stream','application/vnd.ms-excel','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'])){
			echo zy_json(false,'文件读取失败,请检查！',null,103);exit;
		}
		//保存到本地
		$path=APP_PATH.'../public/upload/excel/'.time().'.'.$exts;
		file_put_contents($path, $fileName);
		//读出数据
		$objPHPExcel=\PHPExcel_IOFactory::load($path);
		$reader=$objPHPExcel->getWorksheetiterator();    	
    	foreach ($reader as $sheet ) {
    		//读取表内容
    		$content=$sheet->getRowIterator();
    		$resArray=[];
    		foreach ($content as $key => $items) {
    			$rows=$items->getRowIndex();
    			$columns=$items->getCellIterator();
    			$rowArray=[];
    			if($rows<$data['startRow']){
	    			continue;
	    		}
	    		foreach ($columns as $head => $cell) {
	    			$da=$cell->getValue();
	    			$kid=isset($data['field'][$head])?$data['field'][$head]:$head;
	    			$rowArray[$kid]=$da;
	    		}
	    		$container[]=$rowArray;
    		}
    	}
    	unlink($path);//删除文件
    	echo zy_json(true,'接口调用成功！',$container);exit;
    }

    public function requestDemo(){
    	
    	
		/*//需要导出的数据
		$data=[['id'=>1,'name'=>'天王'],['id'=>2,'name'=>'盖地虎']];
		//表头字段
		$field=[['id','编号'],['name','昵称']];*/
		//发起请求
		//$data=['data'=>$data,'field'=>$field,'aid'=>'fafd4cf8c648da1aa86338d4a5152294','key'=>'86793af7c3bc64fd197001a8be4c6f9a'];
		
		$file="http://gl.300c.cn/upload/task/1558084825.xls";
		$field=['A'=>'id'];
		$data=['filePath'=>$file,'startRow'=>'3','field'=>$field,'aid'=>'fafd4cf8c648da1aa86338d4a5152294','key'=>'86793af7c3bc64fd197001a8be4c6f9a'];
		$data = http_build_query($data);   
		$url="http://gl.300c.cn/zylib/api_file/exportExcel?".$data;
		$ch = curl_init(); 
		curl_setopt ($ch, CURLOPT_URL, $url); 
		curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1); 
		curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT,10); 
		$dxycontent = curl_exec($ch); 
		curl_close($ch);
		dump(json_decode($dxycontent,true));

		//$result = file_get_contents($url);
		//echo $dxycontent;
		/*$path = __DIR__."/".time().".xls";
		$ret  = file_put_contents($path, $dxycontent);
		if ($ret) {
		    echo '保存成功,下载地址：'.$path; // 然后可以取查看文件
		}*/
	}


}
